IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[csp_rptMetric_ProjectsEnding60Details]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[csp_rptMetric_ProjectsEnding60Details]
GO
-- csp_rptMetric_ProjectsEnding60Details 'week', '1/11/2008', 2, '3/15/2009', 11, 0, null, null, 10065
CREATE PROCEDURE [dbo].csp_rptMetric_ProjectsEnding60Details
(
	@PeriodType varchar(50),
	@PeriodStartDate smalldatetime,
	@Institution int =  0,	
	@PeriodEndDate smalldatetime = NULL,
	@Department INT = 0,
	@Unit varchar(1000) = '0',
 	@PrincipalInvestigator varchar(50)='0',
	@Sponsor  varchar(50) = '0',
	@DomainUserId int
)
AS
BEGIN

	if (@PeriodEndDate is null)
		set @PeriodEndDate = @PeriodStartDate
	
	SELECT	pe60.PeriodStartDate,
			ai.Institution,
			ai.Department,
			pe60.ProposalNumber,
			ai.PrincipalInvestigator AS PIName,
			ai.SponsorName AS Sponsor,
			ai.SponsorAgreementId as SponsorId,
			pe60.ProposalStatus,
			pe60.ProjectEnd,
			pe60.PostAwardManager
	FROM  rptMetric_ProjectsEnding60  pe60
	INNER JOIN INSIGHT_RPT_DB.dbo.fnGetSecurityAgreements(@DomainUserId) sec
		ON (sec.FolderNumber = pe60.ProposalNumber)
	INNER JOIN INSIGHT_RPT_DB.dbo.AgrAgreement_Info ai
		ON ai.FolderNumber = pe60.ProposalNumber	
	WHERE pe60.PeriodType = @PeriodType
		AND (ai.institutionId = @Institution OR @Institution =0 OR @Institution is NULL)
		AND (ai.DepartmentId = @Department OR @Department =0 OR @Department is NULL)
		AND (ai.UnitId in (Select * from dbo.fnSplitFundNumbers(@Unit)) OR @Unit='0' OR @unit is null or @unit='') 
		AND  pe60.PeriodStartDate between @PeriodStartDate and IsNULL(@PeriodEndDate,@PeriodStartDate)
		AND (ai.PrincipalInvestigatorId = @PrincipalInvestigator OR @PrincipalInvestigator IS NULL OR @PrincipalInvestigator = '' OR @PrincipalInvestigator = '0')
		AND (ai.SponsorId = @Sponsor OR @Sponsor IS NULL OR @Sponsor = '' OR @Sponsor = '0')

END

GO

